{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Identifying Biased Features Tutorial\n",
    "\n",
    "This tutorial will show you how to identify features that help your models in a way that might just be *too good to be true*. This can happen if there was a problem with the way the dataset was put together, if the machine learning problem wasn't scoped properly, or even because of a bug in one of the feature generators. At times it is hard to understand what a model is really doing, behind the scenes. That's where MLDB's [`classifier.explain`][1] comes to the rescue. In particular, it can help discover that a model is cheating, or in other words, that it has learnt to use bits of information that won't be available when applying the model in real life.\n",
    "\n",
    "To illustrate this, we are going to train a model on some data where we know a feature is biased. You can [find the details here][2]. Basically the task is to predict if the client will subscribe to a term deposit after he receives a call from the bank, given some informations about the client (the employee calling, scocioeconomic conditions at the time, etc.).\n",
    "\n",
    "[1]: ../../../../doc/#builtin/functions/ClassifierExplain.md.html\n",
    "[2]: http://archive.ics.uci.edu/ml/datasets/Bank+Marketing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Setting up\n",
    "\n",
    "The notebook cells below use `pymldb`'s `Connection` class to make [REST API](../../../../doc/#builtin/WorkingWithRest.md.html) calls. You can check out the [Using `pymldb` Tutorial](../../../../doc/nblink.html#_tutorials/Using pymldb Tutorial) for more details."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pymldb\n",
    "mldb = pymldb.Connection()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Importing the data\n",
    "Let's start by importing the data, which we have copied on our servers."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put('/v1/procedures/_', {\n",
    "    'type': 'import.text',\n",
    "    'params': {\n",
    "        'dataFileUrl':\n",
    "            'archive+http://public.mldb.ai/datasets/bank-additional.zip#bank-additional/bank-additional-full.csv',\n",
    "        'outputDataset': 'bank_raw',\n",
    "        'delimiter': ';'\n",
    "        }\n",
    "    })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here is a sneak peek of the data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>age</th>\n",
       "      <th>campaign</th>\n",
       "      <th>\"cons.conf.idx\"</th>\n",
       "      <th>\"cons.price.idx\"</th>\n",
       "      <th>contact</th>\n",
       "      <th>day_of_week</th>\n",
       "      <th>default</th>\n",
       "      <th>duration</th>\n",
       "      <th>education</th>\n",
       "      <th>\"emp.var.rate\"</th>\n",
       "      <th>...</th>\n",
       "      <th>housing</th>\n",
       "      <th>job</th>\n",
       "      <th>loan</th>\n",
       "      <th>marital</th>\n",
       "      <th>month</th>\n",
       "      <th>\"nr.employed\"</th>\n",
       "      <th>pdays</th>\n",
       "      <th>poutcome</th>\n",
       "      <th>previous</th>\n",
       "      <th>y</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>56</td>\n",
       "      <td>1</td>\n",
       "      <td>-36.4</td>\n",
       "      <td>93.994</td>\n",
       "      <td>telephone</td>\n",
       "      <td>mon</td>\n",
       "      <td>no</td>\n",
       "      <td>261</td>\n",
       "      <td>basic.4y</td>\n",
       "      <td>1.1</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>housemaid</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>may</td>\n",
       "      <td>5191</td>\n",
       "      <td>999</td>\n",
       "      <td>nonexistent</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>57</td>\n",
       "      <td>1</td>\n",
       "      <td>-36.4</td>\n",
       "      <td>93.994</td>\n",
       "      <td>telephone</td>\n",
       "      <td>mon</td>\n",
       "      <td>unknown</td>\n",
       "      <td>149</td>\n",
       "      <td>high.school</td>\n",
       "      <td>1.1</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>services</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>may</td>\n",
       "      <td>5191</td>\n",
       "      <td>999</td>\n",
       "      <td>nonexistent</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>37</td>\n",
       "      <td>1</td>\n",
       "      <td>-36.4</td>\n",
       "      <td>93.994</td>\n",
       "      <td>telephone</td>\n",
       "      <td>mon</td>\n",
       "      <td>no</td>\n",
       "      <td>226</td>\n",
       "      <td>high.school</td>\n",
       "      <td>1.1</td>\n",
       "      <td>...</td>\n",
       "      <td>yes</td>\n",
       "      <td>services</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>may</td>\n",
       "      <td>5191</td>\n",
       "      <td>999</td>\n",
       "      <td>nonexistent</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>40</td>\n",
       "      <td>1</td>\n",
       "      <td>-36.4</td>\n",
       "      <td>93.994</td>\n",
       "      <td>telephone</td>\n",
       "      <td>mon</td>\n",
       "      <td>no</td>\n",
       "      <td>151</td>\n",
       "      <td>basic.6y</td>\n",
       "      <td>1.1</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>admin.</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>may</td>\n",
       "      <td>5191</td>\n",
       "      <td>999</td>\n",
       "      <td>nonexistent</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>56</td>\n",
       "      <td>1</td>\n",
       "      <td>-36.4</td>\n",
       "      <td>93.994</td>\n",
       "      <td>telephone</td>\n",
       "      <td>mon</td>\n",
       "      <td>no</td>\n",
       "      <td>307</td>\n",
       "      <td>high.school</td>\n",
       "      <td>1.1</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>services</td>\n",
       "      <td>yes</td>\n",
       "      <td>married</td>\n",
       "      <td>may</td>\n",
       "      <td>5191</td>\n",
       "      <td>999</td>\n",
       "      <td>nonexistent</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>45</td>\n",
       "      <td>1</td>\n",
       "      <td>-36.4</td>\n",
       "      <td>93.994</td>\n",
       "      <td>telephone</td>\n",
       "      <td>mon</td>\n",
       "      <td>unknown</td>\n",
       "      <td>198</td>\n",
       "      <td>basic.9y</td>\n",
       "      <td>1.1</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>services</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>may</td>\n",
       "      <td>5191</td>\n",
       "      <td>999</td>\n",
       "      <td>nonexistent</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>59</td>\n",
       "      <td>1</td>\n",
       "      <td>-36.4</td>\n",
       "      <td>93.994</td>\n",
       "      <td>telephone</td>\n",
       "      <td>mon</td>\n",
       "      <td>no</td>\n",
       "      <td>139</td>\n",
       "      <td>professional.course</td>\n",
       "      <td>1.1</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>admin.</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>may</td>\n",
       "      <td>5191</td>\n",
       "      <td>999</td>\n",
       "      <td>nonexistent</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>41</td>\n",
       "      <td>1</td>\n",
       "      <td>-36.4</td>\n",
       "      <td>93.994</td>\n",
       "      <td>telephone</td>\n",
       "      <td>mon</td>\n",
       "      <td>unknown</td>\n",
       "      <td>217</td>\n",
       "      <td>unknown</td>\n",
       "      <td>1.1</td>\n",
       "      <td>...</td>\n",
       "      <td>no</td>\n",
       "      <td>blue-collar</td>\n",
       "      <td>no</td>\n",
       "      <td>married</td>\n",
       "      <td>may</td>\n",
       "      <td>5191</td>\n",
       "      <td>999</td>\n",
       "      <td>nonexistent</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>24</td>\n",
       "      <td>1</td>\n",
       "      <td>-36.4</td>\n",
       "      <td>93.994</td>\n",
       "      <td>telephone</td>\n",
       "      <td>mon</td>\n",
       "      <td>no</td>\n",
       "      <td>380</td>\n",
       "      <td>professional.course</td>\n",
       "      <td>1.1</td>\n",
       "      <td>...</td>\n",
       "      <td>yes</td>\n",
       "      <td>technician</td>\n",
       "      <td>no</td>\n",
       "      <td>single</td>\n",
       "      <td>may</td>\n",
       "      <td>5191</td>\n",
       "      <td>999</td>\n",
       "      <td>nonexistent</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>25</td>\n",
       "      <td>1</td>\n",
       "      <td>-36.4</td>\n",
       "      <td>93.994</td>\n",
       "      <td>telephone</td>\n",
       "      <td>mon</td>\n",
       "      <td>no</td>\n",
       "      <td>50</td>\n",
       "      <td>high.school</td>\n",
       "      <td>1.1</td>\n",
       "      <td>...</td>\n",
       "      <td>yes</td>\n",
       "      <td>services</td>\n",
       "      <td>no</td>\n",
       "      <td>single</td>\n",
       "      <td>may</td>\n",
       "      <td>5191</td>\n",
       "      <td>999</td>\n",
       "      <td>nonexistent</td>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          age  campaign  \"cons.conf.idx\"  \"cons.price.idx\"    contact  \\\n",
       "_rowName                                                                \n",
       "2          56         1            -36.4            93.994  telephone   \n",
       "3          57         1            -36.4            93.994  telephone   \n",
       "4          37         1            -36.4            93.994  telephone   \n",
       "5          40         1            -36.4            93.994  telephone   \n",
       "6          56         1            -36.4            93.994  telephone   \n",
       "7          45         1            -36.4            93.994  telephone   \n",
       "8          59         1            -36.4            93.994  telephone   \n",
       "9          41         1            -36.4            93.994  telephone   \n",
       "10         24         1            -36.4            93.994  telephone   \n",
       "11         25         1            -36.4            93.994  telephone   \n",
       "\n",
       "         day_of_week  default  duration            education  \"emp.var.rate\"  \\\n",
       "_rowName                                                                       \n",
       "2                mon       no       261             basic.4y             1.1   \n",
       "3                mon  unknown       149          high.school             1.1   \n",
       "4                mon       no       226          high.school             1.1   \n",
       "5                mon       no       151             basic.6y             1.1   \n",
       "6                mon       no       307          high.school             1.1   \n",
       "7                mon  unknown       198             basic.9y             1.1   \n",
       "8                mon       no       139  professional.course             1.1   \n",
       "9                mon  unknown       217              unknown             1.1   \n",
       "10               mon       no       380  professional.course             1.1   \n",
       "11               mon       no        50          high.school             1.1   \n",
       "\n",
       "         ...  housing          job loan  marital month \"nr.employed\"  pdays  \\\n",
       "_rowName ...                                                                  \n",
       "2        ...       no    housemaid   no  married   may          5191    999   \n",
       "3        ...       no     services   no  married   may          5191    999   \n",
       "4        ...      yes     services   no  married   may          5191    999   \n",
       "5        ...       no       admin.   no  married   may          5191    999   \n",
       "6        ...       no     services  yes  married   may          5191    999   \n",
       "7        ...       no     services   no  married   may          5191    999   \n",
       "8        ...       no       admin.   no  married   may          5191    999   \n",
       "9        ...       no  blue-collar   no  married   may          5191    999   \n",
       "10       ...      yes   technician   no   single   may          5191    999   \n",
       "11       ...      yes     services   no   single   may          5191    999   \n",
       "\n",
       "             poutcome previous   y  \n",
       "_rowName                            \n",
       "2         nonexistent        0  no  \n",
       "3         nonexistent        0  no  \n",
       "4         nonexistent        0  no  \n",
       "5         nonexistent        0  no  \n",
       "6         nonexistent        0  no  \n",
       "7         nonexistent        0  no  \n",
       "8         nonexistent        0  no  \n",
       "9         nonexistent        0  no  \n",
       "10        nonexistent        0  no  \n",
       "11        nonexistent        0  no  \n",
       "\n",
       "[10 rows x 21 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "SELECT *\n",
    "FROM bank_raw\n",
    "LIMIT 10\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Training a first model\n",
    "We can train a model on a random selection of 75% of the data, keeping the other 25% for testing."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put('/v1/procedures/_', {\n",
    "    'type': 'classifier.train',\n",
    "    'params': {\n",
    "        'trainingData': \"\"\"\n",
    "            SELECT {* EXCLUDING (y)} AS features,\n",
    "                   y = 'yes' AS label\n",
    "            FROM bank_raw\n",
    "            WHERE rowHash() % 4 != 0\n",
    "            \"\"\",\n",
    "        'modelFileUrl': 'file://bank_model.cls',\n",
    "        'algorithm': 'bbdt',\n",
    "        'functionName': 'score',\n",
    "        'mode': 'boolean'\n",
    "        }\n",
    "    })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This creates a [`classifier`][1] function named \"score\" that we can use on examples from our test set. The higher the score, the more likely the client is going to subscribe. We can try it on examples from our test set.\n",
    "\n",
    "[1]: ../../../../doc/#builtin/functions/ClassifierApply.md.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>score</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>-7.475498</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>-3.350219</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>-2.513726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>-7.387843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>-4.639734</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>-3.118614</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>-2.225752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>-2.396882</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>-2.346199</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>-0.560522</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             score\n",
       "_rowName          \n",
       "12       -7.475498\n",
       "13       -3.350219\n",
       "20       -2.513726\n",
       "22       -7.387843\n",
       "27       -4.639734\n",
       "29       -3.118614\n",
       "31       -2.225752\n",
       "32       -2.396882\n",
       "37       -2.346199\n",
       "40       -0.560522"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "SELECT score({features: {* EXCLUDING (y)}}) AS *\n",
    "FROM bank_raw\n",
    "WHERE rowHash() % 4 = 0\n",
    "LIMIT 10\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now let's see how well our model does on the 25% of the data we didn't train on and get a feel of how good it should perform in real life."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<strong>PUT http://localhost/v1/procedures/_</strong><br /><strong style=\"color: green;\">201 Created</strong><br /> <div class=\"highlight\"><pre style=\"line-height: 125%\"><span></span>{\n",
       "  <span style=\"color: #333333; font-weight: bold\">&quot;status&quot;</span>: {\n",
       "    <span style=\"color: #333333; font-weight: bold\">&quot;firstRun&quot;</span>: {\n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;runStarted&quot;</span>: <span style=\"color: #0000dd\">&quot;2016-12-15T16:31:28.7723858Z&quot;</span>, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;status&quot;</span>: {\n",
       "        <span style=\"color: #333333; font-weight: bold\">&quot;auc&quot;</span>: <span style=\"color: #0000dd\">0.9490764329728899</span>, \n",
       "        <span style=\"color: #333333; font-weight: bold\">&quot;bestMcc&quot;</span>: {\n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;pr&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;recall&quot;</span>: <span style=\"color: #0000dd\">0.8323404255319149</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;f1Score&quot;</span>: <span style=\"color: #0000dd\">0.6522174058019339</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;precision&quot;</span>: <span style=\"color: #0000dd\">0.5361842105263158</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;accuracy&quot;</span>: <span style=\"color: #0000dd\">0.8989830508474577</span>\n",
       "          }, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;mcc&quot;</span>: <span style=\"color: #0000dd\">0.616090015078905</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;gain&quot;</span>: <span style=\"color: #0000dd\">4.711576147816349</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;threshold&quot;</span>: <span style=\"color: #0000dd\">-0.1347393393516541</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;counts&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;falseNegatives&quot;</span>: <span style=\"color: #0000dd\">197.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;truePositives&quot;</span>: <span style=\"color: #0000dd\">978.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;trueNegatives&quot;</span>: <span style=\"color: #0000dd\">8304.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;falsePositives&quot;</span>: <span style=\"color: #0000dd\">846.0</span>\n",
       "          }, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;population&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;included&quot;</span>: <span style=\"color: #0000dd\">1824.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;excluded&quot;</span>: <span style=\"color: #0000dd\">8501.0</span>\n",
       "          }\n",
       "        }, \n",
       "        <span style=\"color: #333333; font-weight: bold\">&quot;bestF1Score&quot;</span>: {\n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;pr&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;recall&quot;</span>: <span style=\"color: #0000dd\">0.774468085106383</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;f1Score&quot;</span>: <span style=\"color: #0000dd\">0.6537356321839081</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;precision&quot;</span>: <span style=\"color: #0000dd\">0.5655686761963953</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;accuracy&quot;</span>: <span style=\"color: #0000dd\">0.9066343825665859</span>\n",
       "          }, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;mcc&quot;</span>: <span style=\"color: #0000dd\">0.6112159939340592</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;gain&quot;</span>: <span style=\"color: #0000dd\">4.969784324874707</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;threshold&quot;</span>: <span style=\"color: #0000dd\">0.03156408667564392</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;counts&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;falseNegatives&quot;</span>: <span style=\"color: #0000dd\">265.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;truePositives&quot;</span>: <span style=\"color: #0000dd\">910.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;trueNegatives&quot;</span>: <span style=\"color: #0000dd\">8451.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;falsePositives&quot;</span>: <span style=\"color: #0000dd\">699.0</span>\n",
       "          }, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;population&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;included&quot;</span>: <span style=\"color: #0000dd\">1609.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;excluded&quot;</span>: <span style=\"color: #0000dd\">8716.0</span>\n",
       "          }\n",
       "        }\n",
       "      }, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;runFinished&quot;</span>: <span style=\"color: #0000dd\">&quot;2016-12-15T16:31:28.946914Z&quot;</span>, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;id&quot;</span>: <span style=\"color: #0000dd\">&quot;2016-12-15T16:31:28.772233Z-463496b56263af05&quot;</span>, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;state&quot;</span>: <span style=\"color: #0000dd\">&quot;finished&quot;</span>\n",
       "    }\n",
       "  }, \n",
       "  <span style=\"color: #333333; font-weight: bold\">&quot;config&quot;</span>: {\n",
       "    <span style=\"color: #333333; font-weight: bold\">&quot;params&quot;</span>: {\n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;outputDataset&quot;</span>: <span style=\"color: #0000dd\">&quot;bank_test&quot;</span>, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;mode&quot;</span>: <span style=\"color: #0000dd\">&quot;boolean&quot;</span>, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;testingData&quot;</span>: <span style=\"color: #0000dd\">&quot;\\n            SELECT score: score({features: {* EXCLUDING (y)}})[score], label: y = &#39;yes&#39;\\n            FROM bank_raw\\n            WHERE rowHash() % 4 = 0\\n            &quot;</span>\n",
       "    }, \n",
       "    <span style=\"color: #333333; font-weight: bold\">&quot;type&quot;</span>: <span style=\"color: #0000dd\">&quot;classifier.test&quot;</span>, \n",
       "    <span style=\"color: #333333; font-weight: bold\">&quot;id&quot;</span>: <span style=\"color: #0000dd\">&quot;_&quot;</span>\n",
       "  }, \n",
       "  <span style=\"color: #333333; font-weight: bold\">&quot;state&quot;</span>: <span style=\"color: #0000dd\">&quot;ok&quot;</span>, \n",
       "  <span style=\"color: #333333; font-weight: bold\">&quot;type&quot;</span>: <span style=\"color: #0000dd\">&quot;classifier.test&quot;</span>, \n",
       "  <span style=\"color: #333333; font-weight: bold\">&quot;id&quot;</span>: <span style=\"color: #0000dd\">&quot;_&quot;</span>\n",
       "}\n",
       "</pre></div>\n"
      ],
      "text/plain": [
       "<Response [201]>"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.put('/v1/procedures/_', {\n",
    "    'type': 'classifier.test',\n",
    "    'params': {\n",
    "        'testingData': \"\"\"\n",
    "            SELECT score: score({features: {* EXCLUDING (y)}})[score], label: y = 'yes'\n",
    "            FROM bank_raw\n",
    "            WHERE rowHash() % 4 = 0\n",
    "            \"\"\",\n",
    "        'outputDataset': 'bank_test',\n",
    "        'mode': 'boolean'\n",
    "        }\n",
    "    })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "As we can see by inspecting the different statistics returned by the classifier.test procedure, that model seems to be doing pretty good! The AUC is 0.95: let's ship this thing in production right now! ... Or let's be cautious!\n",
    "\n",
    "To understand what's going on, let's use the [`classifier.explain` function][1]. This will give us an idea of how much each feature helps (or hurts) in making the predictions.\n",
    "\n",
    "[1]: ../../../../doc/#builtin/functions/ClassifierExplain.md.html"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put('/v1/functions/explain', {\n",
    "    'type': 'classifier.explain',\n",
    "    'params': {\n",
    "        'modelFileUrl': 'file://bank_model.cls'\n",
    "        }\n",
    "    })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can \"explain\" every single example, and know how much each feature influences the final score, like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>bias</th>\n",
       "      <th>explanation.\"\"\"cons.conf.idx\"\"\"</th>\n",
       "      <th>explanation.\"\"\"cons.price.idx\"\"\"</th>\n",
       "      <th>explanation.\"\"\"emp.var.rate\"\"\"</th>\n",
       "      <th>explanation.\"\"\"nr.employed\"\"\"</th>\n",
       "      <th>explanation.age</th>\n",
       "      <th>explanation.campaign</th>\n",
       "      <th>explanation.contact</th>\n",
       "      <th>explanation.day_of_week</th>\n",
       "      <th>explanation.default</th>\n",
       "      <th>...</th>\n",
       "      <th>explanation.education</th>\n",
       "      <th>explanation.euribor3m</th>\n",
       "      <th>explanation.housing</th>\n",
       "      <th>explanation.job</th>\n",
       "      <th>explanation.loan</th>\n",
       "      <th>explanation.marital</th>\n",
       "      <th>explanation.month</th>\n",
       "      <th>explanation.pdays</th>\n",
       "      <th>explanation.poutcome</th>\n",
       "      <th>explanation.previous</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>-0.162905</td>\n",
       "      <td>0.022677</td>\n",
       "      <td>0.093657</td>\n",
       "      <td>1.469614</td>\n",
       "      <td>0.376465</td>\n",
       "      <td>0.074452</td>\n",
       "      <td>-0.021097</td>\n",
       "      <td>-0.047695</td>\n",
       "      <td>0.222051</td>\n",
       "      <td>-0.037208</td>\n",
       "      <td>...</td>\n",
       "      <td>0.037822</td>\n",
       "      <td>0.533715</td>\n",
       "      <td>0.089397</td>\n",
       "      <td>0.189035</td>\n",
       "      <td>-0.008093</td>\n",
       "      <td>-0.012833</td>\n",
       "      <td>0.424642</td>\n",
       "      <td>0.005083</td>\n",
       "      <td>-0.030940</td>\n",
       "      <td>-0.010325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>-0.162905</td>\n",
       "      <td>0.030544</td>\n",
       "      <td>0.090608</td>\n",
       "      <td>0.916880</td>\n",
       "      <td>0.445076</td>\n",
       "      <td>-0.050926</td>\n",
       "      <td>-0.042987</td>\n",
       "      <td>-0.003224</td>\n",
       "      <td>0.031379</td>\n",
       "      <td>0.018937</td>\n",
       "      <td>...</td>\n",
       "      <td>0.106647</td>\n",
       "      <td>0.538069</td>\n",
       "      <td>0.006190</td>\n",
       "      <td>0.014812</td>\n",
       "      <td>-0.008093</td>\n",
       "      <td>0.016223</td>\n",
       "      <td>0.363747</td>\n",
       "      <td>0.005083</td>\n",
       "      <td>-0.012902</td>\n",
       "      <td>-0.018336</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>-0.162905</td>\n",
       "      <td>0.049367</td>\n",
       "      <td>0.061987</td>\n",
       "      <td>0.590823</td>\n",
       "      <td>0.410140</td>\n",
       "      <td>0.024434</td>\n",
       "      <td>-0.036943</td>\n",
       "      <td>0.027854</td>\n",
       "      <td>0.031038</td>\n",
       "      <td>0.018937</td>\n",
       "      <td>...</td>\n",
       "      <td>0.059389</td>\n",
       "      <td>0.568708</td>\n",
       "      <td>0.006190</td>\n",
       "      <td>0.061179</td>\n",
       "      <td>0.051233</td>\n",
       "      <td>-0.012833</td>\n",
       "      <td>0.361657</td>\n",
       "      <td>0.005083</td>\n",
       "      <td>-0.012902</td>\n",
       "      <td>-0.010325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>-0.162905</td>\n",
       "      <td>0.022677</td>\n",
       "      <td>0.122279</td>\n",
       "      <td>1.448991</td>\n",
       "      <td>0.378711</td>\n",
       "      <td>0.009985</td>\n",
       "      <td>-0.023978</td>\n",
       "      <td>-0.003224</td>\n",
       "      <td>0.212649</td>\n",
       "      <td>0.018937</td>\n",
       "      <td>...</td>\n",
       "      <td>0.106647</td>\n",
       "      <td>0.523122</td>\n",
       "      <td>0.089397</td>\n",
       "      <td>0.009036</td>\n",
       "      <td>-0.008093</td>\n",
       "      <td>-0.012833</td>\n",
       "      <td>0.424642</td>\n",
       "      <td>0.005083</td>\n",
       "      <td>-0.030940</td>\n",
       "      <td>-0.010325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>-0.162905</td>\n",
       "      <td>0.027629</td>\n",
       "      <td>0.061987</td>\n",
       "      <td>1.352641</td>\n",
       "      <td>0.394306</td>\n",
       "      <td>0.074452</td>\n",
       "      <td>-0.039815</td>\n",
       "      <td>-0.063717</td>\n",
       "      <td>0.040781</td>\n",
       "      <td>0.018937</td>\n",
       "      <td>...</td>\n",
       "      <td>0.007022</td>\n",
       "      <td>0.430805</td>\n",
       "      <td>-0.005593</td>\n",
       "      <td>0.014591</td>\n",
       "      <td>0.051233</td>\n",
       "      <td>-0.012833</td>\n",
       "      <td>0.600313</td>\n",
       "      <td>0.008857</td>\n",
       "      <td>-0.012902</td>\n",
       "      <td>-0.010325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>-0.162905</td>\n",
       "      <td>0.049367</td>\n",
       "      <td>0.061987</td>\n",
       "      <td>0.802011</td>\n",
       "      <td>0.446966</td>\n",
       "      <td>0.117882</td>\n",
       "      <td>-0.047219</td>\n",
       "      <td>-0.063717</td>\n",
       "      <td>0.040781</td>\n",
       "      <td>-0.037208</td>\n",
       "      <td>...</td>\n",
       "      <td>0.037511</td>\n",
       "      <td>0.548662</td>\n",
       "      <td>-0.007611</td>\n",
       "      <td>0.046389</td>\n",
       "      <td>-0.008093</td>\n",
       "      <td>-0.012833</td>\n",
       "      <td>0.366491</td>\n",
       "      <td>0.005083</td>\n",
       "      <td>-0.012902</td>\n",
       "      <td>-0.010325</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>-0.162905</td>\n",
       "      <td>0.043766</td>\n",
       "      <td>0.057420</td>\n",
       "      <td>0.570200</td>\n",
       "      <td>0.396533</td>\n",
       "      <td>-0.020884</td>\n",
       "      <td>-0.035124</td>\n",
       "      <td>0.023319</td>\n",
       "      <td>0.064483</td>\n",
       "      <td>-0.037208</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.031509</td>\n",
       "      <td>0.568708</td>\n",
       "      <td>-0.073198</td>\n",
       "      <td>0.195006</td>\n",
       "      <td>0.189248</td>\n",
       "      <td>-0.114817</td>\n",
       "      <td>0.282614</td>\n",
       "      <td>0.008857</td>\n",
       "      <td>-0.012902</td>\n",
       "      <td>-0.009871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>-0.162905</td>\n",
       "      <td>0.049367</td>\n",
       "      <td>0.098399</td>\n",
       "      <td>0.570200</td>\n",
       "      <td>0.410140</td>\n",
       "      <td>0.086879</td>\n",
       "      <td>-0.037234</td>\n",
       "      <td>0.063220</td>\n",
       "      <td>0.040781</td>\n",
       "      <td>0.018937</td>\n",
       "      <td>...</td>\n",
       "      <td>0.006318</td>\n",
       "      <td>0.568708</td>\n",
       "      <td>-0.007611</td>\n",
       "      <td>-0.070634</td>\n",
       "      <td>-0.008093</td>\n",
       "      <td>-0.012833</td>\n",
       "      <td>0.358914</td>\n",
       "      <td>0.005083</td>\n",
       "      <td>-0.012902</td>\n",
       "      <td>-0.010436</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>-0.162905</td>\n",
       "      <td>0.049367</td>\n",
       "      <td>0.061987</td>\n",
       "      <td>0.570200</td>\n",
       "      <td>0.454470</td>\n",
       "      <td>-0.020884</td>\n",
       "      <td>-0.035124</td>\n",
       "      <td>-0.063717</td>\n",
       "      <td>0.031038</td>\n",
       "      <td>-0.037208</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.014986</td>\n",
       "      <td>0.568708</td>\n",
       "      <td>0.006190</td>\n",
       "      <td>0.195006</td>\n",
       "      <td>-0.033121</td>\n",
       "      <td>-0.012833</td>\n",
       "      <td>0.358914</td>\n",
       "      <td>0.005083</td>\n",
       "      <td>-0.012902</td>\n",
       "      <td>-0.009871</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>-0.162905</td>\n",
       "      <td>0.033917</td>\n",
       "      <td>0.125016</td>\n",
       "      <td>0.305734</td>\n",
       "      <td>0.410140</td>\n",
       "      <td>0.081519</td>\n",
       "      <td>-0.036943</td>\n",
       "      <td>0.063220</td>\n",
       "      <td>0.040781</td>\n",
       "      <td>0.018937</td>\n",
       "      <td>...</td>\n",
       "      <td>-0.053242</td>\n",
       "      <td>0.426775</td>\n",
       "      <td>0.008209</td>\n",
       "      <td>-0.055832</td>\n",
       "      <td>-0.008093</td>\n",
       "      <td>-0.012833</td>\n",
       "      <td>0.138620</td>\n",
       "      <td>0.006721</td>\n",
       "      <td>-0.012902</td>\n",
       "      <td>-0.010436</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>10 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              bias  explanation.\"\"\"cons.conf.idx\"\"\"  \\\n",
       "_rowName                                              \n",
       "12       -0.162905                         0.022677   \n",
       "13       -0.162905                         0.030544   \n",
       "20       -0.162905                         0.049367   \n",
       "22       -0.162905                         0.022677   \n",
       "27       -0.162905                         0.027629   \n",
       "29       -0.162905                         0.049367   \n",
       "31       -0.162905                         0.043766   \n",
       "32       -0.162905                         0.049367   \n",
       "37       -0.162905                         0.049367   \n",
       "40       -0.162905                         0.033917   \n",
       "\n",
       "          explanation.\"\"\"cons.price.idx\"\"\"  explanation.\"\"\"emp.var.rate\"\"\"  \\\n",
       "_rowName                                                                     \n",
       "12                                0.093657                        1.469614   \n",
       "13                                0.090608                        0.916880   \n",
       "20                                0.061987                        0.590823   \n",
       "22                                0.122279                        1.448991   \n",
       "27                                0.061987                        1.352641   \n",
       "29                                0.061987                        0.802011   \n",
       "31                                0.057420                        0.570200   \n",
       "32                                0.098399                        0.570200   \n",
       "37                                0.061987                        0.570200   \n",
       "40                                0.125016                        0.305734   \n",
       "\n",
       "          explanation.\"\"\"nr.employed\"\"\"  explanation.age  \\\n",
       "_rowName                                                   \n",
       "12                             0.376465         0.074452   \n",
       "13                             0.445076        -0.050926   \n",
       "20                             0.410140         0.024434   \n",
       "22                             0.378711         0.009985   \n",
       "27                             0.394306         0.074452   \n",
       "29                             0.446966         0.117882   \n",
       "31                             0.396533        -0.020884   \n",
       "32                             0.410140         0.086879   \n",
       "37                             0.454470        -0.020884   \n",
       "40                             0.410140         0.081519   \n",
       "\n",
       "          explanation.campaign  explanation.contact  explanation.day_of_week  \\\n",
       "_rowName                                                                       \n",
       "12                   -0.021097            -0.047695                 0.222051   \n",
       "13                   -0.042987            -0.003224                 0.031379   \n",
       "20                   -0.036943             0.027854                 0.031038   \n",
       "22                   -0.023978            -0.003224                 0.212649   \n",
       "27                   -0.039815            -0.063717                 0.040781   \n",
       "29                   -0.047219            -0.063717                 0.040781   \n",
       "31                   -0.035124             0.023319                 0.064483   \n",
       "32                   -0.037234             0.063220                 0.040781   \n",
       "37                   -0.035124            -0.063717                 0.031038   \n",
       "40                   -0.036943             0.063220                 0.040781   \n",
       "\n",
       "          explanation.default          ...           explanation.education  \\\n",
       "_rowName                               ...                                   \n",
       "12                  -0.037208          ...                        0.037822   \n",
       "13                   0.018937          ...                        0.106647   \n",
       "20                   0.018937          ...                        0.059389   \n",
       "22                   0.018937          ...                        0.106647   \n",
       "27                   0.018937          ...                        0.007022   \n",
       "29                  -0.037208          ...                        0.037511   \n",
       "31                  -0.037208          ...                       -0.031509   \n",
       "32                   0.018937          ...                        0.006318   \n",
       "37                  -0.037208          ...                       -0.014986   \n",
       "40                   0.018937          ...                       -0.053242   \n",
       "\n",
       "          explanation.euribor3m  explanation.housing  explanation.job  \\\n",
       "_rowName                                                                \n",
       "12                     0.533715             0.089397         0.189035   \n",
       "13                     0.538069             0.006190         0.014812   \n",
       "20                     0.568708             0.006190         0.061179   \n",
       "22                     0.523122             0.089397         0.009036   \n",
       "27                     0.430805            -0.005593         0.014591   \n",
       "29                     0.548662            -0.007611         0.046389   \n",
       "31                     0.568708            -0.073198         0.195006   \n",
       "32                     0.568708            -0.007611        -0.070634   \n",
       "37                     0.568708             0.006190         0.195006   \n",
       "40                     0.426775             0.008209        -0.055832   \n",
       "\n",
       "          explanation.loan  explanation.marital  explanation.month  \\\n",
       "_rowName                                                             \n",
       "12               -0.008093            -0.012833           0.424642   \n",
       "13               -0.008093             0.016223           0.363747   \n",
       "20                0.051233            -0.012833           0.361657   \n",
       "22               -0.008093            -0.012833           0.424642   \n",
       "27                0.051233            -0.012833           0.600313   \n",
       "29               -0.008093            -0.012833           0.366491   \n",
       "31                0.189248            -0.114817           0.282614   \n",
       "32               -0.008093            -0.012833           0.358914   \n",
       "37               -0.033121            -0.012833           0.358914   \n",
       "40               -0.008093            -0.012833           0.138620   \n",
       "\n",
       "          explanation.pdays  explanation.poutcome  explanation.previous  \n",
       "_rowName                                                                 \n",
       "12                 0.005083             -0.030940             -0.010325  \n",
       "13                 0.005083             -0.012902             -0.018336  \n",
       "20                 0.005083             -0.012902             -0.010325  \n",
       "22                 0.005083             -0.030940             -0.010325  \n",
       "27                 0.008857             -0.012902             -0.010325  \n",
       "29                 0.005083             -0.012902             -0.010325  \n",
       "31                 0.008857             -0.012902             -0.009871  \n",
       "32                 0.005083             -0.012902             -0.010436  \n",
       "37                 0.005083             -0.012902             -0.009871  \n",
       "40                 0.006721             -0.012902             -0.010436  \n",
       "\n",
       "[10 rows x 21 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "SELECT explain({features: {* EXCLUDING (y)}, label: y = 'yes'}) AS *\n",
    "FROM bank_raw\n",
    "WHERE rowHash() % 4 = 0\n",
    "LIMIT 10\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Or you can do the average on all the examples. Here we then transpose the result and sort it by the absolute value."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>explanation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>duration</th>\n",
       "      <td>1.391909</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"\"\"emp.var.rate\"\"\"</th>\n",
       "      <td>0.533359</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>euribor3m</th>\n",
       "      <td>0.367528</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"\"\"nr.employed\"\"\"</th>\n",
       "      <td>0.365880</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <td>0.120450</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>0.030606</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>campaign</th>\n",
       "      <td>0.028834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"\"\"cons.conf.idx\"\"\"</th>\n",
       "      <td>0.024768</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"\"\"cons.price.idx\"\"\"</th>\n",
       "      <td>0.020994</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pdays</th>\n",
       "      <td>0.020351</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day_of_week</th>\n",
       "      <td>0.019881</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>education</th>\n",
       "      <td>0.015184</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>job</th>\n",
       "      <td>0.010417</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>default</th>\n",
       "      <td>0.006594</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>poutcome</th>\n",
       "      <td>0.006276</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>previous</th>\n",
       "      <td>-0.006031</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>contact</th>\n",
       "      <td>0.005695</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>housing</th>\n",
       "      <td>0.002711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>loan</th>\n",
       "      <td>0.001845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>marital</th>\n",
       "      <td>-0.000385</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      explanation\n",
       "_rowName                         \n",
       "duration                 1.391909\n",
       "\"\"\"emp.var.rate\"\"\"       0.533359\n",
       "euribor3m                0.367528\n",
       "\"\"\"nr.employed\"\"\"        0.365880\n",
       "month                    0.120450\n",
       "age                      0.030606\n",
       "campaign                 0.028834\n",
       "\"\"\"cons.conf.idx\"\"\"      0.024768\n",
       "\"\"\"cons.price.idx\"\"\"     0.020994\n",
       "pdays                    0.020351\n",
       "day_of_week              0.019881\n",
       "education                0.015184\n",
       "job                      0.010417\n",
       "default                  0.006594\n",
       "poutcome                 0.006276\n",
       "previous                -0.006031\n",
       "contact                  0.005695\n",
       "housing                  0.002711\n",
       "loan                     0.001845\n",
       "marital                 -0.000385"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "SELECT *\n",
    "FROM transpose((\n",
    "    SELECT avg({explain({features: {* EXCLUDING (y)}, label: y='yes'})[explanation] as *}) AS *\n",
    "    NAMED 'explanation'\n",
    "    FROM bank_raw\n",
    "    WHERE rowHash() % 4 = 0\n",
    "))\n",
    "ORDER BY abs(explanation) DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now what is striking here is that there is one feature that really stands out: `duration`. This is the actual duration of the call. Clearly, that information would not be available in a real life setting: you can't know the duration of a call before it's over, and when it's over you already know if the client has subscribed or not. If you look at the [detailed description of the data][1], you can in fact see a warning saying that using that piece of information is probably a bad idea for any realistic modeling.\n",
    "\n",
    "[1]: http://archive.ics.uci.edu/ml/datasets/Bank+Marketing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Retraining without the biased feature\n",
    "Now that we have identified the feature that is responsible for those suspiciously good results, let's train and test again, but adding `duration` to the excluded columns so that it is not used by the model."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put('/v1/procedures/_', {\n",
    "    'type': 'classifier.train',\n",
    "    'params': {\n",
    "        'trainingData': \"\"\"\n",
    "            SELECT {* EXCLUDING (y, duration)} AS features,\n",
    "                   y = 'yes' AS label\n",
    "            FROM bank_raw\n",
    "            WHERE rowHash() % 4 != 0\n",
    "            \"\"\",\n",
    "        'modelFileUrl': 'file://bank_model.cls',\n",
    "        'algorithm': 'bbdt',\n",
    "        'functionName': 'score',\n",
    "        'mode': 'boolean'\n",
    "        }\n",
    "    })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<strong>PUT http://localhost/v1/procedures/_</strong><br /><strong style=\"color: green;\">201 Created</strong><br /> <div class=\"highlight\"><pre style=\"line-height: 125%\"><span></span>{\n",
       "  <span style=\"color: #333333; font-weight: bold\">&quot;status&quot;</span>: {\n",
       "    <span style=\"color: #333333; font-weight: bold\">&quot;firstRun&quot;</span>: {\n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;runStarted&quot;</span>: <span style=\"color: #0000dd\">&quot;2016-12-15T16:31:30.566227Z&quot;</span>, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;status&quot;</span>: {\n",
       "        <span style=\"color: #333333; font-weight: bold\">&quot;auc&quot;</span>: <span style=\"color: #0000dd\">0.7986184862225458</span>, \n",
       "        <span style=\"color: #333333; font-weight: bold\">&quot;bestMcc&quot;</span>: {\n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;pr&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;recall&quot;</span>: <span style=\"color: #0000dd\">0.5557446808510639</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;f1Score&quot;</span>: <span style=\"color: #0000dd\">0.5011511895625479</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;precision&quot;</span>: <span style=\"color: #0000dd\">0.4563242487770789</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;accuracy&quot;</span>: <span style=\"color: #0000dd\">0.87409200968523</span>\n",
       "          }, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;mcc&quot;</span>: <span style=\"color: #0000dd\">0.4326346653063816</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;gain&quot;</span>: <span style=\"color: #0000dd\">4.00982797329646</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;threshold&quot;</span>: <span style=\"color: #0000dd\">-0.1314646601676941</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;counts&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;falseNegatives&quot;</span>: <span style=\"color: #0000dd\">522.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;truePositives&quot;</span>: <span style=\"color: #0000dd\">653.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;trueNegatives&quot;</span>: <span style=\"color: #0000dd\">8372.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;falsePositives&quot;</span>: <span style=\"color: #0000dd\">778.0</span>\n",
       "          }, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;population&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;included&quot;</span>: <span style=\"color: #0000dd\">1431.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;excluded&quot;</span>: <span style=\"color: #0000dd\">8894.0</span>\n",
       "          }\n",
       "        }, \n",
       "        <span style=\"color: #333333; font-weight: bold\">&quot;bestF1Score&quot;</span>: {\n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;pr&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;recall&quot;</span>: <span style=\"color: #0000dd\">0.5557446808510639</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;f1Score&quot;</span>: <span style=\"color: #0000dd\">0.5011511895625479</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;precision&quot;</span>: <span style=\"color: #0000dd\">0.4563242487770789</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;accuracy&quot;</span>: <span style=\"color: #0000dd\">0.87409200968523</span>\n",
       "          }, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;mcc&quot;</span>: <span style=\"color: #0000dd\">0.4326346653063816</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;gain&quot;</span>: <span style=\"color: #0000dd\">4.00982797329646</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;threshold&quot;</span>: <span style=\"color: #0000dd\">-0.1314646601676941</span>, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;counts&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;falseNegatives&quot;</span>: <span style=\"color: #0000dd\">522.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;truePositives&quot;</span>: <span style=\"color: #0000dd\">653.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;trueNegatives&quot;</span>: <span style=\"color: #0000dd\">8372.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;falsePositives&quot;</span>: <span style=\"color: #0000dd\">778.0</span>\n",
       "          }, \n",
       "          <span style=\"color: #333333; font-weight: bold\">&quot;population&quot;</span>: {\n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;included&quot;</span>: <span style=\"color: #0000dd\">1431.0</span>, \n",
       "            <span style=\"color: #333333; font-weight: bold\">&quot;excluded&quot;</span>: <span style=\"color: #0000dd\">8894.0</span>\n",
       "          }\n",
       "        }\n",
       "      }, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;runFinished&quot;</span>: <span style=\"color: #0000dd\">&quot;2016-12-15T16:31:30.732396Z&quot;</span>, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;id&quot;</span>: <span style=\"color: #0000dd\">&quot;2016-12-15T16:31:30.566155Z-463496b56263af05&quot;</span>, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;state&quot;</span>: <span style=\"color: #0000dd\">&quot;finished&quot;</span>\n",
       "    }\n",
       "  }, \n",
       "  <span style=\"color: #333333; font-weight: bold\">&quot;config&quot;</span>: {\n",
       "    <span style=\"color: #333333; font-weight: bold\">&quot;params&quot;</span>: {\n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;outputDataset&quot;</span>: <span style=\"color: #0000dd\">&quot;bank_test&quot;</span>, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;mode&quot;</span>: <span style=\"color: #0000dd\">&quot;boolean&quot;</span>, \n",
       "      <span style=\"color: #333333; font-weight: bold\">&quot;testingData&quot;</span>: <span style=\"color: #0000dd\">&quot;\\n            SELECT score: score({features: {* EXCLUDING (y)}})[score], label: y = &#39;yes&#39;\\n            FROM bank_raw\\n            WHERE rowHash() % 4 = 0\\n            &quot;</span>\n",
       "    }, \n",
       "    <span style=\"color: #333333; font-weight: bold\">&quot;type&quot;</span>: <span style=\"color: #0000dd\">&quot;classifier.test&quot;</span>, \n",
       "    <span style=\"color: #333333; font-weight: bold\">&quot;id&quot;</span>: <span style=\"color: #0000dd\">&quot;_&quot;</span>\n",
       "  }, \n",
       "  <span style=\"color: #333333; font-weight: bold\">&quot;state&quot;</span>: <span style=\"color: #0000dd\">&quot;ok&quot;</span>, \n",
       "  <span style=\"color: #333333; font-weight: bold\">&quot;type&quot;</span>: <span style=\"color: #0000dd\">&quot;classifier.test&quot;</span>, \n",
       "  <span style=\"color: #333333; font-weight: bold\">&quot;id&quot;</span>: <span style=\"color: #0000dd\">&quot;_&quot;</span>\n",
       "}\n",
       "</pre></div>\n"
      ],
      "text/plain": [
       "<Response [201]>"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.put('/v1/procedures/_', {\n",
    "    'type': 'classifier.test',\n",
    "    'params': {\n",
    "        'testingData': \"\"\"\n",
    "            SELECT score: score({features: {* EXCLUDING (y)}})[score], label: y = 'yes'\n",
    "            FROM bank_raw\n",
    "            WHERE rowHash() % 4 = 0\n",
    "            \"\"\",\n",
    "        'outputDataset': 'bank_test',\n",
    "        'mode': 'boolean'\n",
    "        }\n",
    "    })"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now a AUC of 0.80 sounds more reasonable!\n",
    "\n",
    "If we run the explanation again, the highest ranking features seem more legitimate."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<Response [201]>\n"
     ]
    }
   ],
   "source": [
    "print mldb.put('/v1/functions/explain', {\n",
    "    'type': 'classifier.explain',\n",
    "    'params': {\n",
    "        'modelFileUrl': 'file://bank_model.cls'\n",
    "        }\n",
    "    })"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>explanation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>_rowName</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>euribor3m</th>\n",
       "      <td>0.419913</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"\"\"nr.employed\"\"\"</th>\n",
       "      <td>0.215071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"\"\"emp.var.rate\"\"\"</th>\n",
       "      <td>0.071023</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>campaign</th>\n",
       "      <td>0.027480</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>poutcome</th>\n",
       "      <td>0.019888</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"\"\"cons.conf.idx\"\"\"</th>\n",
       "      <td>0.019198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>month</th>\n",
       "      <td>0.018006</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pdays</th>\n",
       "      <td>0.011858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>contact</th>\n",
       "      <td>0.010918</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>age</th>\n",
       "      <td>0.010127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>\"\"\"cons.price.idx\"\"\"</th>\n",
       "      <td>0.007808</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>day_of_week</th>\n",
       "      <td>0.005137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>job</th>\n",
       "      <td>0.004162</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>previous</th>\n",
       "      <td>-0.003373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>marital</th>\n",
       "      <td>0.003092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>education</th>\n",
       "      <td>0.002887</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>loan</th>\n",
       "      <td>0.001451</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>default</th>\n",
       "      <td>0.001364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>housing</th>\n",
       "      <td>0.000671</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      explanation\n",
       "_rowName                         \n",
       "euribor3m                0.419913\n",
       "\"\"\"nr.employed\"\"\"        0.215071\n",
       "\"\"\"emp.var.rate\"\"\"       0.071023\n",
       "campaign                 0.027480\n",
       "poutcome                 0.019888\n",
       "\"\"\"cons.conf.idx\"\"\"      0.019198\n",
       "month                    0.018006\n",
       "pdays                    0.011858\n",
       "contact                  0.010918\n",
       "age                      0.010127\n",
       "\"\"\"cons.price.idx\"\"\"     0.007808\n",
       "day_of_week              0.005137\n",
       "job                      0.004162\n",
       "previous                -0.003373\n",
       "marital                  0.003092\n",
       "education                0.002887\n",
       "loan                     0.001451\n",
       "default                  0.001364\n",
       "housing                  0.000671"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mldb.query(\"\"\"\n",
    "SELECT *\n",
    "FROM transpose((\n",
    "    SELECT avg({explain({features: {* EXCLUDING (y)}, label: y='yes'})[explanation] as *}) AS *\n",
    "    NAMED 'explanation'\n",
    "    FROM bank_raw\n",
    "    WHERE rowHash() % 4 = 0\n",
    "))\n",
    "ORDER BY abs(explanation) DESC\n",
    "\"\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion\n",
    "We have shown how to use MLDB to identify \"too good to be true\" features when training a model. Keep in mind that features that really help are not necessarily biased, they might just be really good features! Understanding your data is key, and the tool presented here makes it much simpler."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Where to next?\n",
    "\n",
    "Check out the other [Tutorials and Demos](../../../../doc/#builtin/Demos.md.html)."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
